package com.xyht.sca_s.student_manage_system.modules.trainingSchool.service.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.wechat.pay.java.service.partnerpayments.jsapi.model.PrepayWithRequestPaymentResponse;
import com.xyht.sca_s.student_manage_system.common.commonReturn.CommonCodeEnum;
import com.xyht.sca_s.student_manage_system.common.commonReturn.CommonResult;
import com.xyht.sca_s.student_manage_system.common.commonReturn.ExceptionCast;
import com.xyht.sca_s.student_manage_system.common.commonReturn.ResponseResult;
import com.xyht.sca_s.student_manage_system.common.util.TimeUtil;
import com.xyht.sca_s.student_manage_system.modules.chargePayment.entity.resp.PrepayPaymentResp;
import com.xyht.sca_s.student_manage_system.modules.payment.wxpay.config.WxPayConfig;
import com.xyht.sca_s.student_manage_system.modules.payment.wxpay.entity.PrepayPaymentReq;
import com.xyht.sca_s.student_manage_system.modules.payment.wxpay.entity.WxPayFunctions;
import com.xyht.sca_s.student_manage_system.modules.payment.wxpay.util.WxPayUtil;
import com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.entity.RxRegistrationChargePaymentCategoryDivide;
import com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.entity.RxRegistrationChargePaymentRecord;
import com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.entity.resp.RxRegistrationChargePaymentRecordResp;
import com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.mapper.RxRegistrationChargePaymentCategoryDivideMapper;
import com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.mapper.RxRegistrationChargePaymentRecordMapper;
import com.xyht.sca_s.student_manage_system.modules.signUp.constant.SmsSignUpCommonConstant;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.entity.*;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.entity.req.SmsTsTrainingInfoReq;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.entity.req.TrainingInfoBatchExportReq;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.entity.resp.SmsTsTrainingInfoResp;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.mapper.*;
import com.xyht.sca_s.student_manage_system.modules.trainingSchool.service.SmsTsTrainingInfoService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.BeanUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.AsyncResult;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

import static com.xyht.sca_s.student_manage_system.common.util.ParamCheckUtil.*;
import static com.xyht.sca_s.student_manage_system.modules.advanceRegistration.constant.IsPayConstant.IS_PAY;
import static com.xyht.sca_s.student_manage_system.modules.advanceRegistration.constant.IsPayConstant.IS_PAY_NOT;
import static com.xyht.sca_s.student_manage_system.modules.chargePayment.constant.SmsWnsChargePaymentRecordConstant.CHARGE_PAYMENT_RECORD_MODE_ONLINE;
import static com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.constant.PaymentTypeConstant.RX_REGISTRATION_CHARGE_PAYMENT_RECORD_TYPE_DES_TRAINING;
import static com.xyht.sca_s.student_manage_system.modules.rxRegistrationChargePayment.constant.PaymentTypeConstant.RX_REGISTRATION_CHARGE_PAYMENT_RECORD_TYPE_TRAINING;
import static com.xyht.sca_s.student_manage_system.modules.student.constant.StudentInfoCommonConstant.PER_SHEET_NUM;

/**
 * <p>
 * 培训信息表 服务实现类
 * </p>
 *
 * @author XYHT
 * @since 2024-05-18
 */
@Service
public class SmsTsTrainingInfoServiceImpl extends ServiceImpl<SmsTsTrainingInfoMapper, SmsTsTrainingInfo> implements SmsTsTrainingInfoService {

    @Resource
    private SmsTsJobMapper smsTsJobMapper;
    @Resource
    private SmsTsWorkerLevelMapper smsTsWorkerLevelMapper;
    @Resource
    private SmsTsChargeItemAmountMapper smsTsChargeItemAmountMapper;
    @Resource
    private RxRegistrationChargePaymentRecordMapper rxRegistrationChargePaymentRecordMapper;
    @Resource
    private RxRegistrationChargePaymentCategoryDivideMapper rxRegistrationChargePaymentCategoryDivideMapper;
    @Resource
    private WxPayUtil wxPayUtil;
    @Resource
    private SmsTsFeeCategoryMapper smsTsFeeCategoryMapper;
    @Resource
    private SmsTsChargeItemMapper smsTsChargeItemMapper;
    @Resource
    private SmsTsUnitPlaceMapper smsTsUnitPlaceMapper;

    @Override
    @Transactional
    public ResponseResult payment(SmsTsTrainingInfoReq req) {
        if (isNullOrEmpty(req.getName(), req.getIdCard(), req.getJobId(), req.getLevelId(), req.getItemAmountIdList(), req.getOpenId())) {
            return CommonResult.failed(CommonCodeEnum.INVALID_PARAM);
        }

        if (!isValidIDCard(req.getIdCard())) {
            return CommonResult.failed(CommonCodeEnum.INVALID_PARAM);
        }

        // 校验工种和等级是否与选择收费项匹配
        SmsTsJob job = smsTsJobMapper.selectById(req.getJobId());
        if (job == null) {
            return CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_JOB_NOT_EXIST);
        }

        SmsTsWorkerLevel level = smsTsWorkerLevelMapper.selectById(req.getLevelId());
        if (level == null) {
            return CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_WORKER_LEVEL_NOT_EXIST);
        }

        List<SmsTsChargeItemAmount> itemAmountList = smsTsChargeItemAmountMapper.selectBatchIds(req.getItemAmountIdList());
        if (itemAmountList.size() != req.getItemAmountIdList().size()) {
            return CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_CHARGE_ITEM_AMOUNT_NOT_EXIST);
        }

        // 校验实操考试费和实操考试材料费是否同步选择
        boolean hasItemId2 = itemAmountList.stream().anyMatch(itemAmount -> itemAmount.getItemId().equals("2"));
        boolean hasItemId5 = itemAmountList.stream().anyMatch(itemAmount -> itemAmount.getItemId().equals("5"));
        if (hasItemId2 != hasItemId5) { // 检查两者是否同时存在或同时不存在
            return CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_TWO_FEE_NEED_MATCH);
        }

        if (itemAmountList.stream().anyMatch(itemAmount -> !itemAmount.getCategoryId().equals(job.getCategoryId()) || !itemAmount.getLevelId().equals(level.getId()))) {
            return CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_CHARGE_ITEM_AMOUNT_NOT_MATCH);
        }

        // 生成支付记录
        RxRegistrationChargePaymentRecord paymentRecord = new RxRegistrationChargePaymentRecord();
        paymentRecord.setType(RX_REGISTRATION_CHARGE_PAYMENT_RECORD_TYPE_TRAINING);
        paymentRecord.setMode(CHARGE_PAYMENT_RECORD_MODE_ONLINE);
        WxPayFunctions wxPayFunction = WxPayConfig.wxPayFunctionsList.get(0);
        paymentRecord.setConfigName(wxPayFunction.getWxPayCommonConfig().getCONFIG_NAME());
        Date timeExpire = TimeUtil.AddMinute(new Date(), 30); // 设置过期时间(订单失效时间)
        paymentRecord.setExpireTime(timeExpire);
        rxRegistrationChargePaymentRecordMapper.insert(paymentRecord);

        // 统计总金额
        BigDecimal totalAmount = new BigDecimal(0);
        for (SmsTsChargeItemAmount itemAmount : itemAmountList) {
            // 生成金额分配数据
            RxRegistrationChargePaymentCategoryDivide categoryDivide = new RxRegistrationChargePaymentCategoryDivide();
            categoryDivide.setPaymentRecordId(paymentRecord.getId());
            categoryDivide.setCategoryId(itemAmount.getCategoryId());
            categoryDivide.setPlanId(itemAmount.getId()); // 收费项金额id存在了分类详情id
            categoryDivide.setAmount(itemAmount.getAmount());
            rxRegistrationChargePaymentCategoryDivideMapper.insert(categoryDivide);

            totalAmount = totalAmount.add(itemAmount.getAmount());
        }

        // 查询培训信息是否重复
        SmsTsTrainingInfo existTrainingInfo = this.getOne(new LambdaQueryWrapper<SmsTsTrainingInfo>()
                .eq(SmsTsTrainingInfo::getName, req.getName())
                .eq(SmsTsTrainingInfo::getIdCard, req.getIdCard())
                .eq(SmsTsTrainingInfo::getJobId, req.getJobId())
                .eq(SmsTsTrainingInfo::getLevelId, req.getLevelId())
                .eq(SmsTsTrainingInfo::getIsPaid, IS_PAY)
                .eq(SmsTsTrainingInfo::getIsDeleted, false));
        if (existTrainingInfo != null) {
            ExceptionCast.cast(CommonResult.failed(CommonCodeEnum.SCHOOL_TRAINING_INFO_REPEAT));
        }

        // 生成培训信息
        SmsTsTrainingInfo trainingInfo = new SmsTsTrainingInfo();
        BeanUtils.copyProperties(req, trainingInfo, "id");
        trainingInfo.setItemAmountIdList(JSON.toJSONString(req.getItemAmountIdList()));
        trainingInfo.setTotalAmount(totalAmount);
        trainingInfo.setPayAmount(BigDecimal.ZERO);
        trainingInfo.setIsPaid(false);
        this.save(trainingInfo);

        // 移除无效数据(超过半小时未支付)
        List<RxRegistrationChargePaymentRecord> ivalidPaymentRecordList = rxRegistrationChargePaymentRecordMapper.selectList(new LambdaQueryWrapper<RxRegistrationChargePaymentRecord>()
                .le(RxRegistrationChargePaymentRecord::getExpireTime, new Date())
                .isNull(RxRegistrationChargePaymentRecord::getPaymentTime));
        if (!ivalidPaymentRecordList.isEmpty()) {
            List<String> ivalidPaymentRecordIdList = ivalidPaymentRecordList.stream().map(RxRegistrationChargePaymentRecord::getId).distinct().collect(Collectors.toList());
            rxRegistrationChargePaymentRecordMapper.deleteBatchIds(ivalidPaymentRecordIdList);
            rxRegistrationChargePaymentCategoryDivideMapper.delete(new LambdaQueryWrapper<RxRegistrationChargePaymentCategoryDivide>()
                    .in(RxRegistrationChargePaymentCategoryDivide::getPaymentRecordId, ivalidPaymentRecordIdList));
        }

        // 发起支付
        PrepayPaymentReq prepayPaymentReq = new PrepayPaymentReq();
        prepayPaymentReq.setDescription(RX_REGISTRATION_CHARGE_PAYMENT_RECORD_TYPE_DES_TRAINING);
        prepayPaymentReq.setOutTradeNo(paymentRecord.getId());
        prepayPaymentReq.setTimeExpire(timeExpire);
        // 设置订单金额 单位：分(订单金额 = 原金额(元) * 100)
        int total = totalAmount.multiply(BigDecimal.valueOf(100)).intValue();
        prepayPaymentReq.setTotal(total);
        prepayPaymentReq.setOpenId(req.getOpenId());
        prepayPaymentReq.setNotifyUrl("tsPayCallback");

        // 发起支付
        PrepayWithRequestPaymentResponse prepayWithRequestPaymentResponse = wxPayUtil.prepayPayment(prepayPaymentReq, wxPayFunction);
        if (prepayWithRequestPaymentResponse == null) {
            ExceptionCast.cast(CommonResult.failed(CommonCodeEnum.WNS_PAYMENT_PRE_PAYMENT_INFO_MISSING));
        }

        // 设置预支付信息
        PrepayPaymentResp prepayPaymentResp = new PrepayPaymentResp();
        BeanUtils.copyProperties(prepayWithRequestPaymentResponse, prepayPaymentResp);
        prepayPaymentResp.setTimestamp(prepayWithRequestPaymentResponse.getTimeStamp());
        prepayPaymentResp.setIsPay(IS_PAY_NOT);

        // 构造返回数据
        RxRegistrationChargePaymentRecordResp resp = new RxRegistrationChargePaymentRecordResp();
        BeanUtils.copyProperties(paymentRecord, resp);
        resp.setPrepayPaymentResp(prepayPaymentResp);
        resp.setPrepayId(JSON.toJSONString(prepayPaymentResp));

        // 记录预支付数据
        paymentRecord.setPlanId(trainingInfo.getId()); // 将培训信息id传入planId
        paymentRecord.setPrepayId(JSON.toJSONString(prepayPaymentResp));
        paymentRecord.setConfigName(wxPayFunction.getWxPayCommonConfig().getCONFIG_NAME());
        paymentRecord.setWxOpenId(req.getOpenId());
        rxRegistrationChargePaymentRecordMapper.updateById(paymentRecord);

        return CommonResult.success(resp);
    }

    @Override
    public ResponseResult update(SmsTsTrainingInfoReq req) {
        if (isNullOrEmpty(req.getId())) {
            return CommonResult.failed(CommonCodeEnum.INVALID_COlUMN);
        }

        SmsTsTrainingInfo trainingInfo = this.getById(req.getId());
        if (trainingInfo == null) {
            return CommonResult.failed(CommonCodeEnum.INVALID_COlUMN);
        }
        BeanUtils.copyProperties(req, trainingInfo, "jobId", "levelId", "itemAmountIdList", "totalAmount", "payAmount", "isPaid");

        this.updateById(trainingInfo);

        return CommonResult.success();
    }

    @Override
    public ResponseResult list(Integer pageNum, Integer pageSize, String jobId, String levelId, Boolean isPaid, String ss, Integer isDeleted) {
        LambdaQueryWrapper<SmsTsTrainingInfo> queryWrapper = new LambdaQueryWrapper<SmsTsTrainingInfo>()
                .eq(SmsTsTrainingInfo::getIsDeleted, isDeleted)
                .orderByDesc(SmsTsTrainingInfo::getUpdateTime);

        if (!isNullOrEmpty(jobId)) {
            queryWrapper.eq(SmsTsTrainingInfo::getJobId, jobId);
        }

        if (!isNullOrEmpty(levelId)) {
            queryWrapper.eq(SmsTsTrainingInfo::getLevelId, levelId);
        }

        if (isPaid != null) {
            queryWrapper.eq(SmsTsTrainingInfo::getIsPaid, isPaid);
        }

        if (!isNullOrEmpty(ss)) {
            queryWrapper.and(wrapper -> wrapper.like(SmsTsTrainingInfo::getName, ss)
                    .or().like(SmsTsTrainingInfo::getName, ss)
                    .or().like(SmsTsTrainingInfo::getIdCard, ss)
                    .or().like(SmsTsTrainingInfo::getTel, ss)
                    .or().like(SmsTsTrainingInfo::getLandline, ss)
                    .or().like(SmsTsTrainingInfo::getNote, ss)
                    .or().like(SmsTsTrainingInfo::getWorkUnit, ss));
        }

        List<SmsTsChargeItem> itemList = smsTsChargeItemMapper.selectList(new LambdaQueryWrapper<SmsTsChargeItem>()
                .orderByAsc(SmsTsChargeItem::getId));

        Page<SmsTsTrainingInfo> page = new Page<>(pageNum, pageSize);
        this.page(page, queryWrapper);
        List<SmsTsTrainingInfoResp> collect = page.getRecords().stream().map(item -> {
            SmsTsTrainingInfoResp resp = new SmsTsTrainingInfoResp();
            BeanUtils.copyProperties(item, resp);
            SmsTsJob job = smsTsJobMapper.selectById(item.getJobId());
            if (job != null) {
                resp.setJobName(job.getName());
            }

            SmsTsWorkerLevel level = smsTsWorkerLevelMapper.selectById(item.getLevelId());
            if (level != null) {
                resp.setLevelName(level.getName());
            }

            if (!isNullOrEmpty(item.getItemAmountIdList())) {
                List<String> itemAmountIdList = JSON.parseArray(item.getItemAmountIdList(), String.class);
                List<SmsTsChargeItemAmount> itemAmountList = smsTsChargeItemAmountMapper.selectBatchIds(itemAmountIdList);

                // 把原始的list放入map
                Map<String, SmsTsChargeItemAmount> map = new HashMap<>();
                for (SmsTsChargeItemAmount itemAmount : itemAmountList) {
                    map.put(itemAmount.getItemId(), itemAmount);
                }

                // 按照itemList的顺序创建排序后的itemAmountList
                List<SmsTsChargeItemAmount> sortedItemAmountList = new ArrayList<>();
                for (SmsTsChargeItem chargeItem : itemList) {
                    // 检查是否存在对应的item，若存在则添加到sortedItemList
                    SmsTsChargeItemAmount itemAmount = map.get(chargeItem.getId());
                    sortedItemAmountList.add(itemAmount);
                }

                resp.setItemAmountList(sortedItemAmountList);
            }

            if (!isNullOrEmpty(item.getUnitPlaceId())) {
                SmsTsUnitPlace unitPlace = smsTsUnitPlaceMapper.selectById(item.getUnitPlaceId());
                if (unitPlace != null) {
                    resp.setUnitPlaceName(unitPlace.getName());
                }
            }

            return resp;
        }).collect(Collectors.toList());

        return CommonResult.success(collect, (int) page.getTotal());
    }

    @Override
    @Async
    public Future<ResponseResult> batchExport(TrainingInfoBatchExportReq req, HttpServletResponse response) {
        LambdaQueryWrapper<SmsTsTrainingInfo> queryWrapper = new LambdaQueryWrapper<SmsTsTrainingInfo>()
                .eq(SmsTsTrainingInfo::getIsDeleted, 0)
                .orderByDesc(SmsTsTrainingInfo::getUpdateTime);

        if (!isNullOrEmpty(req.getJobId())) {
            queryWrapper.eq(SmsTsTrainingInfo::getJobId, req.getJobId());
        }

        if (!isNullOrEmpty(req.getLevelId())) {
            queryWrapper.eq(SmsTsTrainingInfo::getLevelId, req.getLevelId());
        }

        if (req.getIsPaid() != null) {
            queryWrapper.eq(SmsTsTrainingInfo::getIsPaid, req.getIsPaid());
        }

        if (isLegalDateRange(req.getStartDate(), req.getEndDate())) {
            queryWrapper.between(SmsTsTrainingInfo::getUpdateTime, req.getStartDate(), req.getEndDate());
        }

        if (!isNullOrEmpty(req.getSs())) {
            queryWrapper.and(wrapper -> wrapper.like(SmsTsTrainingInfo::getName, req.getSs())
                    .or().like(SmsTsTrainingInfo::getIdCard, req.getSs())
                    .or().like(SmsTsTrainingInfo::getTel, req.getSs())
                    .or().like(SmsTsTrainingInfo::getLandline, req.getSs())
                    .or().like(SmsTsTrainingInfo::getWorkUnit, req.getSs()));
        }

        // 获取导出数据
        List<SmsTsTrainingInfo> trainingInfoList = this.list(queryWrapper);

        // 获取所有收费项
        List<SmsTsChargeItem> itemList = smsTsChargeItemMapper.selectList(new LambdaQueryWrapper<SmsTsChargeItem>()
                .orderByAsc(SmsTsChargeItem::getId));

        try {
            // 3. 导出数据
            // 声明一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 获取sheet页数
            double sheetNum = Math.ceil((double) trainingInfoList.size() / PER_SHEET_NUM);
            for (int k = 0; k < sheetNum; k++) {
                // 生成一个 sheet页,设置 sheet页 名称为 "培训信息"
                HSSFSheet sheet = workbook.createSheet("培训信息" + (k + 1));
                // 设置表格列宽度为 12
                sheet.setDefaultColumnWidth(12);

                // 设置单元格的显示样式
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                // 设置单元格字体
                HSSFFont font = workbook.createFont();
                font.setFontHeightInPoints((short) 10); // 字号
                font.setBold(true); // 加粗
                cellStyle.setFont(font);

                // 3.1 设置第一行数据
                // 创建第一行表头
                HSSFRow headRow = sheet.createRow(0);
                int fixColumnNum = 20; // 固定列数
                int columnNum = fixColumnNum; // 列数
                for (int i = 0; i < columnNum; i++) {
                    // 创建一个单元格
                    HSSFCell cell = headRow.createCell(i);
                    cell.setCellStyle(cellStyle);

                    String columnName = "";
                    switch (i) {
                        case 0:
                            columnName = "序号";
                            break;
                        case 1:
                            columnName = "姓名";
                            break;
                        case 2:
                            columnName = "身份证号";
                            break;
                        case 3:
                            columnName = "手机号";
                            break;
                        case 4:
                            columnName = "单位座机号";
                            break;
                        case 5:
                            columnName = "报考工种";
                            break;
                        case 6:
                            columnName = "工种类别";
                            break;
                        case 7:
                            columnName = "工种代码";
                            break;
                        case 8:
                            columnName = "报考等级";
                            break;
                        case 9:
                        case 10:
                        case 11:
                        case 12:
                        case 13:
                        case 14:
                        case 15:
                            columnName = itemList.get(i - 9).getName();
                            break;
                        case 16:
                            columnName = "缴费合计";
                            break;
                        case 17:
                            columnName = "工作单位";
                            break;
                        case 18:
                            columnName = "单位属地";
                            break;
                        case 19:
                            columnName = "备注";
                            break;
                        default:
                            columnName = "";
                    }

                    // 创建一个内容对象
                    HSSFRichTextString text = new HSSFRichTextString(columnName);
                    // 将内容对象的文字内容写入到单元格中
                    cell.setCellValue(text);
                }

                // 设置内容
                int range = SmsSignUpCommonConstant.PER_SHEET_NUM;
                // 如果是最后一个 sheet 页, 修改 range 值
                if (k + 1 == sheetNum) {
                    range = trainingInfoList.size() - SmsSignUpCommonConstant.PER_SHEET_NUM * k;
                }
                // 3.2 设置其他行数据
                for (int i = 0; i < range; i++) {
                    // 获取每条数据并设置
                    SmsTsTrainingInfo trainingInfo = trainingInfoList.get(PER_SHEET_NUM * k + i);

                    // 获取工种信息
                    SmsTsJob job = smsTsJobMapper.selectById(trainingInfo.getJobId());

                    // 获取类别数据
                    SmsTsFeeCategory category = smsTsFeeCategoryMapper.selectById(job.getCategoryId());

                    // 获取等级信息
                    SmsTsWorkerLevel level = smsTsWorkerLevelMapper.selectById(trainingInfo.getLevelId());


                    List<String> itemAmountIdList = JSON.parseArray(trainingInfo.getItemAmountIdList(), String.class);
                    List<SmsTsChargeItemAmount> itemAmountList = smsTsChargeItemAmountMapper.selectBatchIds(itemAmountIdList);

                    // 把原始的list放入map
                    Map<String, SmsTsChargeItemAmount> map = new HashMap<>();
                    for (SmsTsChargeItemAmount itemAmount : itemAmountList) {
                        map.put(itemAmount.getItemId(), itemAmount);
                    }

                    // 按照itemList的顺序创建排序后的itemAmountList
                    BigDecimal totalAmount = new BigDecimal(0);
                    List<SmsTsChargeItemAmount> sortedItemAmountList = new ArrayList<>();
                    for (SmsTsChargeItem item : itemList) {
                        // 检查是否存在对应的item，若存在则添加到sortedItemList
                        SmsTsChargeItemAmount itemAmount;
                        if (map.get(item.getId()) == null) {
                            itemAmount = new SmsTsChargeItemAmount();
                            itemAmount.setAmount(BigDecimal.ZERO);
                        } else {
                            itemAmount = map.get(item.getId());
                        }
                        sortedItemAmountList.add(itemAmount);
                        totalAmount = totalAmount.add(itemAmount.getAmount());
                    }

                    // 设置数据
                    // 创建一行
                    HSSFRow row = sheet.createRow(i + 1);
                    // 序号
                    row.createCell(0).setCellValue(new HSSFRichTextString(String.valueOf(i + 1)));
                    // 设置除序号外其他数据
                    for (int j = 1; j < columnNum; j++) {
                        String data = "";
                        try {
                            switch (j) {
                                case 1: // 姓名
                                    data = trainingInfo.getName();
                                    break;
                                case 2: // 身份证号
                                    data = trainingInfo.getIdCard();
                                    break;
                                case 3: // 手机号
                                    data = trainingInfo.getTel();
                                    break;
                                case 4: // 座机号
                                    data = trainingInfo.getLandline();
                                    break;
                                case 5: // 报考工种
                                    data = job.getName();
                                    break;
                                case 6: // 工种类别
                                    data = category.getName();
                                    break;
                                case 7: // 工种代码
                                    data = job.getCode();
                                    break;
                                case 8: // 报考等级
                                    data = level.getName();
                                    break;
                                case 9: // 理论考试
                                case 10: // 实操考试
                                case 11: // 理论培训
                                case 12: // 实操培训
                                case 13: // 实操考试材料
                                case 14: // 教材
                                case 15: // 综合评审费
                                    SmsTsChargeItemAmount itemAmount = sortedItemAmountList.get(j - 9);
                                    data = String.valueOf(itemAmount.getAmount());
                                    break;
                                case 16: // 缴费合计
                                    data = String.valueOf(totalAmount);
                                    break;
                                case 17: // 工作单位
                                    data = trainingInfo.getWorkUnit();
                                    break;
                                case 18: // 单位属地
                                    if (!isNullOrEmpty(trainingInfo.getUnitPlaceId())) {
                                        SmsTsUnitPlace unitPlace = smsTsUnitPlaceMapper.selectById(trainingInfo.getUnitPlaceId());
                                        if (unitPlace != null) {
                                            data = unitPlace.getName();
                                        }
                                    }
                                    break;
                                case 19: // 备注
                                    data = trainingInfo.getNote();
                                    break;
                                default:
                                    data = "";
                            }
                        } catch (Exception e) {
                            data = "";
                        }
                        row.createCell(j).setCellValue(new HSSFRichTextString(data));
                    }
                }
            }

            // 设置 response
            // response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("培训信息.xlsx", "utf-8"));
            response.setCharacterEncoding("utf-8");
            response.setHeader("file-type", "download");
            response.setHeader("file-name", URLEncoder.encode("培训信息.xlsx", "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "file-type,file-name");
            // 刷新缓冲
            response.flushBuffer();
            // workbook将Excel写入到response的输出流中，供页面下载
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            workbook.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            return new AsyncResult<>(CommonResult.failed(CommonCodeEnum.FAIL));
        }
        return null;
    }
}
